//
//  HMStatusDAL.swift
//  Weibo
//
//  Created by 黄亦寒 on 2016/10/9.
//  Copyright © 2016年 HM. All rights reserved.
//

import UIKit
//删除7天前的缓存数据
let MaxTimeInterval:TimeInterval = 7 * 24 * 3600

//数据库访问类，用于加载网络请求和缓存新浪微博数据
class HMStatusDAL: NSObject {

    //加载数据
    class func loadData(accessToken:String,maxId:Int64,sinceId:Int64,callBack:@escaping ([[String:Any]]) ->()) {
        //检查本地是否有缓存数据
        let localArray = checkCacheData(maxId: maxId, sinceId: sinceId)
        //如果本地有数据直接返回
        if localArray.count > 0 {
            //回调本地数据
            callBack(localArray)
            return
        }
        
        //如果本地没有数据直接从网络加载
        NetworkTools.sharedTools.requestStatuses(accessToken: accessToken ,maxId:maxId,sinceId:sinceId) { (response, error) in
            if error != nil{
                print("网络请求异常:\(error)")
                callBack(localArray)
                return
            }
            
            //执行到此说明请求成功,返回的数据是字典
            //print("---\(response)")
            
            guard let dic = response as?[String:Any] else {
                print("字典格式不正确")
                callBack(localArray)
                return
            }
            
            //执行到此，说明格式正确，取出字典中的status数组，数组里面是字典
            guard let statusArray = dic["statuses"] as?[[String:Any]] else {
                print("字典格式不正确")
                callBack(localArray)
                return
            }
            
            //执行到此表示网络请求成功，数据加载成功后缓存到本地
            cacheData(statusDicArray: statusArray)
            //数据缓存完后直接把数据返回
            callBack(statusArray)
            
    }
    
}
    
    //查询本地微博数据
    class func checkCacheData(maxId:Int64,sinceId:Int64) -> [[String:Any]] {
        //sql语句
        var sql = "SELECT * FROM statuses\n"
        if maxId > 0 {
            //代表上拉加载
            sql += "where statusid < \(maxId)\n"
        }else{
            //代表下拉刷新
            sql += "where statusid > \(sinceId)\n"
        }
        
        //拼接用户id
        sql += "and userid = \(HMUserAccountViewModel.sharedHMUserAccountViewModel.userAccount!.uid)\n"
        //拼接排序方式
        sql += "order by statusid desc\n"
        //拼接返回数据的最大数量
        sql += "limit 20\n"
        
        //查询结果
        let dicArray = SqliteManager.sharedManager.selectDicArray(sql: sql)
        //存储微博字典数据
        var tempArray = [[String:Any]]()
        //遍历数组字典获取微博数据
        for dic in dicArray {
            //获取微博二进制数据
            let statusData = dic["status"]! as! Data
            //获取微博字典
            let statusDic = try!JSONSerialization.jsonObject(with: statusData, options: []) as! [String:Any]
            tempArray.append(statusDic)
            
            
        }
        
        return tempArray
        
        
    }
    
    //缓存新浪微博数据
    class func cacheData(statusDicArray:[[String:Any]]) {
        //sql语句
        let sql = "INSERT OR REPLACE INTO Statuses(statusid,status,userid) VALUES(?,?,?)"
        //获取登录用户的id
        let userid = HMUserAccountViewModel.sharedHMUserAccountViewModel.userAccount?.uid
        //使用事务保存数据
        SqliteManager.sharedManager.queue.inTransaction { (db, rollBack) in
            //遍历数组字典依次插入数据
            for statusDic in statusDicArray {
                //获取微博id
                let statusid = statusDic["id"]!
                //把微博数据转成二进制存储
                let statusData = try!JSONSerialization.data(withJSONObject: statusDic, options: [])
                //插入数据
                let result = db?.executeUpdate(sql, withArgumentsIn: [statusid,statusData,userid!])
                
                if result == false {
                    //如果插入失败就进行回滚
                    rollBack?.pointee = true
                    break
                }
            }
        }
        
        
    }
    
    //清除缓存数据
    class func clearCacheData() {
        //设置删除时间
        let deleteDate = Date().addingTimeInterval(-MaxTimeInterval)
        let dt = DateFormatter()
        //指定本地化信息
        dt.locale = Locale(identifier: "en_US")
        //设置时间格式化方式
        dt.dateFormat = "yyyy-MM-dd HH:mm:ss"
        //获取删除时间字符串
        let deleteDateString = dt.string(from: deleteDate)
        //sql语句
        let sql = "DELETE FROM statuses where time < '\(deleteDateString)'"
        
        SqliteManager.sharedManager.queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: nil)
            if result == true {
                print("缓存数据删除成功，影响了\(db?.changes())条数据")
            }else {
                print("缓存数据删除失败")
            }
        }
        
        
    }
}
